package com.yzl.meeting.business.dao.impl;

import com.yzl.meeting.business.dao.MangeUserDao;
import com.yzl.meeting.business.dto.ShowUser;
import com.yzl.meeting.business.entity.Delegation;
import com.yzl.meeting.business.entity.PageBean;
import com.yzl.meeting.business.entity.Staff;
import com.yzl.meeting.business.utils.MyDbUtils;
import com.yzl.meeting.business.vo.NewUserVo;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

/**
 * @Title: MangeUserDaoImpl
 * @Author: 姚志立
 * @Description:
 * @Date: 2020/10/14 11:00
 * @Version: 1.0
 */
public class MangeUserDaoImpl implements MangeUserDao {
    private QueryRunner qr = MyDbUtils.getQr();
    @Override
    public List<Delegation> listDelegation() throws Exception {
        String sql = "select * from t_delegation";
        List<Delegation> query = qr.query(sql, new BeanListHandler<Delegation>(Delegation.class));
        return query;
    }

    @Override
    public List<Staff> listStaff(Integer delegationId) throws Exception {
        String sql = "select * from t_staff where delegationId=?";
        Object[] params = {delegationId};
        List<Staff> query = qr.query(sql, new BeanListHandler<Staff>(Staff.class),params);
        return query;
    }


    @Override
    public Integer addUser(NewUserVo newUserVo) throws SQLException {
        Connection conn = MyDbUtils.getConnection();
        String sql = "insert into t_user (userName,telPhone,password,userRealName,gender) values (?,?,?,?,?)";
        String sql2 = "select last_insert_id() from t_user";
        Object[] params = {newUserVo.getUserName(),newUserVo.getTelPhone(),newUserVo.getPassword(),
        newUserVo.getUserRealName(),newUserVo.getGender()};
        qr.update(conn,sql, params);
        String s = qr.query(conn,sql2, new ScalarHandler()).toString();
        return Integer.valueOf(s);
    }

    @Override
    public void addUserStaff(Integer uId, Integer staffId) throws SQLException {
        Connection conn = MyDbUtils.getConnection();
        String sql="insert into t_user_staff values (?,?)";
        Object[]params={uId,staffId};
        qr.update(conn,sql, params);
    }

    @Override
    public PageBean findAllUser(PageBean pageBean) throws SQLException {
        int pageSize = pageBean.getPageSize();
        int pageCode = pageBean.getCurrentPage();
        String sql="SELECT u.uId,u.userName,u.userRealName,u.gender,u.telPhone,s.staffName,d.delegationName" +
                " FROM t_user u,t_user_staff us,t_staff s,t_delegation d" +
                " where u.uId=us.uId AND us.staffId=s.staffId AND d.delegationId=s.delegationId limit ?,?;";
        Object[] params = {(pageCode-1)*pageSize, pageSize};
        List<ShowUser> userList =  qr.query(sql, new BeanListHandler<ShowUser>(ShowUser.class), params);
        sql = "SELECT COUNT(*) FROM t_user u,t_user_staff us,t_staff s,t_delegation d"+
                " where u.uId=us.uId AND us.staffId=s.staffId AND d.delegationId=s.delegationId;";
        Number number = qr.query(sql, new ScalarHandler<>());
        int totalRecords = number.intValue();
        pageBean.setTotalRecords(totalRecords);
        pageBean.setDatas(userList);
        return pageBean;
    }

    @Override
    public void deleteUserRole(Integer uId) throws SQLException {
        String sql = "delete from t_user_staff where uId=?";
        Object[] params = {uId};
        qr.update(sql, params);
    }

    @Override
    public void deleteUser(Integer uId) throws SQLException {
        String sql = "delete from t_user where uId=?";
        Object[] params = {uId};
        qr.update(sql, params);
    }

    @Override
    public NewUserVo findUserById(Integer uId) throws SQLException {
        String sql="select * from t_user u,t_user_staff us ,t_staff s where u.uId=us.uId and s.staffId=us.staffId and u.uId=?";
        Object[] params = {uId};
        NewUserVo query = qr.query(sql, new BeanHandler<NewUserVo>(NewUserVo.class), params);
        return query;
    }

    @Override
    public void updateUser(NewUserVo newUserVo) throws SQLException {
        String sql = "UPDATE t_user SET userName=?,telPhone=?,password=?," +
                "userRealName=?,gender=? WHERE uId=?";
        Object[] params = {newUserVo.getUserName(), newUserVo.getTelPhone(), newUserVo.getPassword(),
                newUserVo.getUserRealName(),newUserVo.getGender(),newUserVo.getUid()};
        qr.update(sql, params);
    }

    @Override
    public void updateUserStaff(Integer uId, Integer staffId) throws SQLException {
        String sql = "UPDATE t_user_staff set staffId=? where uId=?";
        Object[] params = {staffId,uId};
        qr.update(sql, params);
    }

    @Override
    public List<Staff> listAllStaff() throws SQLException {
        String sql = "select * from t_staff";
        List<Staff> query = qr.query(sql, new BeanListHandler<Staff>(Staff.class));
        return query;
    }
}
